//
//  DBTool.swift
//  QRCode
//
//  Created by zzk176 on 12/12/16.
//  Copyright © 2016 zzk176. All rights reserved.
//

import FMDB

class DBTool : NSObject{
    let DATABASE_NAME = "WILLDO_CONTACT"
    let TABLE_NAME = "ExternalMember"
    
    static var tool:DBTool?
    
    class func shareDBTool()->DBTool{
        if tool == nil{
            tool = DBTool()
        }
        return tool!
    }
    
    private func openDB() -> FMDatabase?{
        
        let fileURL = try! FileManager.default
            .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("\(DATABASE_NAME)"+".sqlite")
        print(fileURL)
        let dataBase = FMDatabase(path: fileURL.path)
        guard dataBase.open() else {
            print("not open")
            return nil
        }
        return dataBase
    }
    
    func createTable(){
        guard let dataBase = openDB() else{
            return
        }
        do{
            try dataBase.executeUpdate("create table if not exists \(TABLE_NAME)(id integer primary key autoincrement, name text, pos text, tel text, note text, avatar blob)", values: nil)
        }catch{
            print(error.localizedDescription)
            dataBase.close()
            return
        }
        dataBase.close()
    }
    
    func addContactDB(data:ExternalMemberModel){
        guard let dataBase = openDB() else{
            return
        }
        
        let sql = "insert into \(TABLE_NAME)(name, pos, tel, note, avatar) values( ?, ?, ?, ?, ?)"
        
        do{
            try dataBase.executeUpdate("create table if not exists \(TABLE_NAME)(id integer primary key autoincrement, name text, pos text, tel text, note text, avatar blob)", values: nil)
            try dataBase.executeUpdate(sql, values: [data.name,data.position,data.tel,data.note,data.avatar])
        }catch let error as NSError{
            print("failed: \(error.localizedDescription)")
            dataBase.close()
            return
        }
        dataBase.close()
    }

    // MARK: 加载数据
    func loadData()-> [ExternalMemberModel]?{

        var models = [ExternalMemberModel]()

        guard let dataBase = openDB() else{
            return nil
        }

        let sql = "select * from \(TABLE_NAME)"
        do{
            let rs = try dataBase.executeQuery(sql, values: nil)
            while rs.next(){
                let modle = ExternalMemberModel(id: rs.long(forColumn: "id"), name: rs.string(forColumn: "name")!, position: rs.string(forColumn: "pos")!, tel: rs.string(forColumn: "tel")!, note: rs.string(forColumn: "note")!, avatar: rs.data(forColumn: "avatar")!)
                models.append(modle)
            }
        }catch let error as NSError{
            print("failed: \(error.localizedDescription)")
            dataBase.close()
            return nil
        }
        dataBase.close()

        return models
    }

    func removeDataById(id:Int){
        guard let dataBase = openDB() else{
            return
        }

        let sql = "delete from \(TABLE_NAME) where id = \(id)"

        do{
            try dataBase.executeUpdate(sql, values: nil)
        }catch let error as NSError{
            print("failed: \(error.localizedDescription)")
            dataBase.close()
            return
        }
        dataBase.close()
    }

    func delTable(){
        guard let dataBase = openDB() else{
            return
        }

        do{
            try dataBase.executeUpdate("drop table "+"\(TABLE_NAME)", values: nil)
            try dataBase.executeUpdate("create table if not exists \(TABLE_NAME)(id integer primary key autoincrement, name text, pos text, tel text, note text, avatar blob)", values: nil)
        }catch let error as NSError{
            print("failed: \(error.localizedDescription)")
            dataBase.close()
            return
        }
        dataBase.close()
    }

}
